package com.rabbiter.si.common;

import com.rabbiter.si.entity.StudentCourseTeacher;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class ExcelUtils {

    // 导出学生课程教师信息到Excel
    public static ByteArrayInputStream exportToExcel(List<StudentCourseTeacher> sctList) {
        try (SXSSFWorkbook workbook = new SXSSFWorkbook(100);
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {

            Sheet sheet = workbook.createSheet("学生课程教师信息");

            // 启用列宽自动调整
            ((SXSSFSheet) sheet).trackAllColumnsForAutoSizing();

            // 创建表头
            Row headerRow = sheet.createRow(0);
            String[] headers = {"记录ID", "学生ID", "课程ID", "教师ID", "成绩", "学期"};
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            }

            // 填充数据
            for (int i = 0; i < sctList.size(); i++) {
                Row row = sheet.createRow(i + 1);
                StudentCourseTeacher sct = sctList.get(i);

                row.createCell(0).setCellValue(Objects.requireNonNullElse(sct.getSctid(), 0));
                row.createCell(1).setCellValue(Objects.requireNonNullElse(sct.getSid(), 0));
                row.createCell(2).setCellValue(Objects.requireNonNullElse(sct.getCid(), 0));
                row.createCell(3).setCellValue(Objects.requireNonNullElse(sct.getTid(), 0));
                row.createCell(4).setCellValue(Objects.requireNonNullElse(sct.getGrade(), 0.0f));
                row.createCell(5).setCellValue(Objects.requireNonNullElse(sct.getTerm(), ""));
            }

            // 自动调整列宽
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
                // 适当增加列宽，避免内容显示不全
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 15 / 10);
            }

            workbook.write(out);
            return new ByteArrayInputStream(out.toByteArray());
        } catch (IOException e) {
            throw new RuntimeException("导出Excel失败", e);
        }
    }

    // 从Excel导入学生课程教师信息
    public static List<StudentCourseTeacher> importFromExcel(InputStream inputStream) {
        List<StudentCourseTeacher> sctList = new ArrayList<>();
        try (Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getLastRowNum();

            // 从第2行开始读取数据（第1行为表头）
            for (int i = 1; i <= rowCount; i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                StudentCourseTeacher sct = new StudentCourseTeacher();
                sct.setSctid(getIntValue(row.getCell(0)));
                sct.setSid(getIntValue(row.getCell(1)));
                sct.setCid(getIntValue(row.getCell(2)));
                sct.setTid(getIntValue(row.getCell(3)));
                sct.setGrade(getFloatValue(row.getCell(4)));
                sct.setTerm(getStringValue(row.getCell(5)));

                sctList.add(sct);
            }
        } catch (IOException e) {
            throw new RuntimeException("导入Excel失败", e);
        }
        return sctList;
    }

    // 辅助方法：获取整数类型值
    private static Integer getIntValue(Cell cell) {
        if (cell == null) return null;
        switch (cell.getCellType()) {
            case NUMERIC:
                return (int) cell.getNumericCellValue();
            case STRING:
                try {
                    return Integer.parseInt(cell.getStringCellValue());
                } catch (NumberFormatException e) {
                    return null;
                }
            default:
                return null;
        }
    }

    // 辅助方法：获取浮点类型值
    private static Float getFloatValue(Cell cell) {
        if (cell == null) return null;
        switch (cell.getCellType()) {
            case NUMERIC:
                return (float) cell.getNumericCellValue();
            case STRING:
                try {
                    return Float.parseFloat(cell.getStringCellValue());
                } catch (NumberFormatException e) {
                    return null;
                }
            default:
                return null;
        }
    }

    // 辅助方法：获取字符串类型值
    private static String getStringValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                }
                return String.valueOf((long) cell.getNumericCellValue());
            default:
                return "";
        }
    }
}